package org.example.demojdbc.repository;

import org.example.demojdbc.dox.User;


import org.example.demojdbc.dto.UserAdressDTO;
import org.example.demojdbc.dto.UserCountDTO;
import org.example.demojdbc.mapper.UserMapper;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserRepository extends CrudRepository<User,String> {
    @Query(value = """
            select * from user u,address a where u.id=a.user_id and u.id=:uid""",resultSetExtractorClass = UserMapper.class)
    UserAdressDTO findByUserId(@Param("uid") String uid);
    @Query("""
            select u.id,u.name,count(a.user_id) as count
            from user u left join address a
            on u.id = a.user_id
            group by u.id
            order by count""")
    List<UserCountDTO> findCounts();
}
